#!/local/bin/powersh
#
# Run Server Audit Process
#
# File: run_audit.ps1
#
# Author: Nem W Schlecht
#   Copyright (C) 2014
# License: Creative Commons Attribution-Share Alike
#
# Description: Gather data from SQL Servers and store in the audit
#     database
#
# Creation Date: 2014-11-17
#


#
# Settings
#

#$central_management_server = "corpi-14967"
$central_management_server = "novial"
$central_management_db = "msdb"

#$audit_server = "corpi-14967\dba"
$audit_server = "novial"
$audit_db = "ServerAudit"

#
# Functions
#

Function Run-Query ([string]$SqlServer `
					, [string]$SqlDatabase `
					, [string]$SqlQuery) {

#	Write-Host "Server: $SqlServer"
#	Write-Host "Database: $SqlDatabase"
#	Write-Host "Query: $SqlQuery"

    $ArrResults = @()

    # Accessing Data Base
    $SqlServListCon = New-Object -TypeName System.Data.SqlClient.SqlConnection
    $SqlServListCon.ConnectionString = "Data Source=$SqlServer;"
    $SqlServListCon.ConnectionString += "Initial Catalog=$SqlDatabase;"
    $SqlServListCon.ConnectionString += "Integrated Security=true;"
    $SqlServListCon.Open()
    $SqlCmd = $SqlServListCon.CreateCommand()
    $SqlCmd.CommandText = $SqlQuery

    $result = $SqlCmd.ExecuteReader()
    $table = new-object "System.Data.DataTable"
    $table.load($result)
    foreach ($row in $table) {
        $ArrResults += $row
	}

    $SqlServListCon.Close()

    return $ArrResults
}

Function Load-Audit-Data-On-Server ([string]$SqlServer `
					, [string]$SqlDatabase `
					, $DataTable) {

    # Accessing Data Base
    $SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Data Source=$audit_server;"
    $SqlConnection.ConnectionString += "Initial Catalog=$audit_db;"
    $SqlConnection.ConnectionString += "Integrated Security=true;"
    $SqlConnection.Open()

	$table = "auditData"

	$bulkcopy = new-object ("System.Data.SqlClient.SqlBulkCopy") $SqlConnection
	$bulkcopy.DestinationTableName = $table
	$bulkcopy.WriteToServer($DataTable)

    $SqlConnection.Close()
}

function Run-Audit-Script-On-Server () {
	
}

function Setup-New-Day (){

    # Accessing Data Base
    $SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Data Source=$audit_server;"
    $SqlConnection.ConnectionString += "Initial Catalog=$audit_db;"
    $SqlConnection.ConnectionString += "Integrated Security=true;"
    $SqlConnection.Open()
#    $SqlCmd.CommandText = $SqlQuery
#    $result = $SqlCmd.ExecuteReader()

	$history_create = "
		INSERT INTO auditHistory
		OUTPUT INSERTED.histId
		DEFAULT VALUES
	"

    $SqlCmdCreateHistory = $SqlConnection.CreateCommand()
    $SqlCmdCreateHistory.CommandText = $history_create
    $result = $SqlCmdCreateHistory.ExecuteReader()
	$table = new-object "System.Data.DataTable"
    $table.load($result)
    foreach ($row in $table) {
        $ArrResults += $row
	}

    $SqlConnection.Close()

	return $ArrResults.histId
}


#
# Main Program
#

$server_list_query = "
	SELECT DISTINCT
	--TOP(5)
	server_name
	FROM sysmanagement_shared_registered_servers_internal
	WHERE description NOT LIKE '%noaudit%'
	ORDER BY server_name
"

$server_list_res = Run-Query $central_management_server `
							$central_management_db `
							$server_list_query

# $server_list | Export-Csv -NoTypeInformation -Path "C:\temp\test.csv"

$fetch_audit_query = "
	SELECT queryId
		, queryOrder
		, queryName
		, query
	FROM auditQueries
	WHERE isActive = 1
	ORDER BY queryOrder
"

$fetch_audit_query_res = Run-Query $audit_server `
							$audit_db `
							$fetch_audit_query

$audit_query = ""
foreach ($query_rec in $fetch_audit_query_res) {
	$audit_query += "-- " `
		+ $query_rec.queryName `
		+ " (" + $query_rec.queryId + ")"
	$audit_query += $query_rec.query
	$audit_query += "`n"
}

$dt = New-Object "System.Data.DataTable"
$dt.Columns.Add("histId") | Out-Null
$dt.Columns.Add("ServerName") | Out-Null
$dt.Columns.Add("ikey") | Out-Null
$dt.Columns.Add("ivalue") | Out-Null

$histid = Setup-New-Day

foreach ($server_rec in $server_list_res) {
	Write-Host "Server:" $server_rec.server_name

	$audit_query_res = Run-Query $server_rec.server_name `
								"master" `
								$audit_query
	foreach ($audit_data_rec in $audit_query_res) {
#		Write-Host $audit_data_rec.ikey

		$row = $dt.NewRow()
		$row["histId"] = $histid
		$row["ServerName"] = $audit_data_rec.ServerName
		$row["ikey"] = $audit_data_rec.ikey;
		$row["ivalue"] = $audit_data_rec.ivalue;
		$dt.Rows.Add($row);
	}
}

# $dt | Format-Table -AutoSize

Load-Audit-Data-On-Server $audit_server `
						$audit_db `
						$dt

